package com.sinosoft.platform.common.util;

import java.awt.Color;
import java.io.FileOutputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;

/**
 * Excel操作工具类
 * 
 * @date 2010-11-27
 * @since JDK 1.5
 * @author dingkui
 * @version 1.0
 * 
 */
public class ExcelUtil {
    /**
     * 合并单元格
     * @param sheet Excel的Sheet对象
     * @param startRow 
     *      起始行
     * @param startCol 
     *      起始列
     * @param endRow 
     *      结束行
     * @param endCol 
     *      结束列
     * @throws Exception 
     *      异常
     */
    public static void mergedRegion(Sheet sheet, int startRow, int startCol, int endRow, int endCol) throws Exception {
        if (sheet != null) {
            sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol - 1));
        }
        else {
            throw new RuntimeException();
        }
    }

    /**
     * 设定某行的值
     * 
     * @param sheet
     *            Excel的Sheet对象
     * @param values
     *            待写入的List
     * @param rowNum
     *            行号
     */
    @SuppressWarnings("unchecked")
    public static void getReportRow(Sheet sheet, List values, int rowNum) {
        if (values == null) {
            return;
        }
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        for (int i = 0; i < values.size(); i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                cell = row.createCell(i);
            }
            cell.setCellValue("" + (String) values.get(i));
        }
    }

    /**
     * 设定某单元格的值
     * 
     * @param sheet
     *            Excel的Sheet对象
     * @param value
     *            待写入的String
     * @param rowNum
     *            单元格行号
     * @param colNum
     *            单元格列号
     */
    public static void getReportCell(Sheet sheet, String value, int rowNum, int colNum) {
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        Cell csCell = row.getCell(colNum);
        if (csCell == null) {
            csCell = row.createCell(colNum);
        }
        csCell.setCellValue("" + value);
    }

    /**
     * 生成excel文件
     * 
     * @param workbook
     *            POI的workbook对象
     * @param fileName
     *            文件名
     */
    public static void writeToFile(Workbook workbook, String fileName) {
        try {
            FileOutputStream fileOut = new FileOutputStream(fileName);
            workbook.write(fileOut);
            fileOut.close();
        }
        catch (Exception e) {
            throw new RuntimeException("保存Excel文件出错！");
        }
    }

    /**
     * 获取指定单元格上的值
     * 
     * @param sheet
     *            sheet
     * @param rowNum
     *            Excel的行
     * @param colNum
     *            Excel的列
     * @return 值对象
     */
    public static Object getValue(Sheet sheet, int rowNum, int colNum) {
        Object obj = null;

        if (sheet != null) {
            Row row = sheet.getRow(rowNum);

            if (row != null) {
                Cell cell = row.getCell(colNum);

                if (cell != null) {
                    int cellType = cell.getCellType();

                    if (cellType == Cell.CELL_TYPE_NUMERIC) {
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            obj = cell.getDateCellValue();
                            if (obj == null) {
                                obj = "";
                            }
                        }
                        else {
                            try {
                                obj = new Double(cell.getNumericCellValue());
                            }
                            catch (NumberFormatException ex) {
                                obj = cell.getDateCellValue();

                                return obj;
                            }
                        }
                    }
                    else if (cellType == Cell.CELL_TYPE_STRING) {
                        obj = String.valueOf(cell.getStringCellValue());
                    }
                    else if (cellType == Cell.CELL_TYPE_FORMULA) {
                        obj = String.valueOf(cell.getNumericCellValue());
                    }
                    else if (cellType == Cell.CELL_TYPE_BLANK) {
                        obj = cell.getStringCellValue();
                    }
                    else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                        obj = Boolean.valueOf(cell.getBooleanCellValue());
                    }
                    else {
                        obj = "";
                    }
                }
            }
        }
        if (obj instanceof String) {
            String str = (String) obj;
            str = StringUtil.replace(str, "　", "").trim();
            obj = str;
        }
        return obj;
    }

    /**
     * 克隆字体
     * 
     * @param font
     *            要克隆的字体
     * @return 克隆的字体
     */
    public static Font cloneFont(Font font) {
        Workbook wb = new HSSFWorkbook();
        Font newFont = wb.createFont();
        newFont.setFontName(font.getFontName());
        newFont.setFontHeightInPoints(font.getFontHeightInPoints());
        newFont.setColor(font.getColor());
        newFont.setBoldweight(font.getBoldweight());
        newFont.setItalic(font.getItalic());
        newFont.setStrikeout(font.getStrikeout());
        newFont.setUnderline(font.getUnderline());
        return newFont;
    }

    /**
     * 取得执行的HSSFColor颜色
     * @param color 顏色
     * @return HSSF顏色
     */
    public static HSSFColor getHSSFColor(Color color) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFPalette palette = wb.getCustomPalette();
        HSSFColor hssfColor = palette.findColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue());
        if (hssfColor == null) {
            hssfColor = palette.addColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue());
        }
        return hssfColor;
    }

    /**
     * 取得执行的XSSFColor颜色
     * @param color 顏色
     * @return HSSF顏色
     */
    public static XSSFColor getXSSFColor(Color color) {
        XSSFColor xssfColor = new XSSFColor();
        byte[] rgb = new byte[3];
        rgb[0] = (byte) color.getRed();
        rgb[1] = (byte) color.getGreen();
        rgb[2] = (byte) color.getBlue();
        xssfColor.setRgb(rgb);
        return xssfColor;
    }

    /**
     * 
      * 新建sheet
      * 
      * @param wb
      *       参数
      * @param sheetname
      *         参数
      * @return Sheet
     */
    public static Sheet createSheetForUpdate(Workbook wb, String sheetname) {
        return wb.createSheet(sheetname);
    }

}
